import requests
import pymysql
import re

#链接MySQL取商品ID构造评论的url
conn = pymysql.connect("106.14.144.54","root","Wzl!13433627612","京东商品数据")
cur = conn.cursor()
cur.execute('SELECT `商品ID` FROM `京东手机数据`')
header= {
    'accept': '*/*',
    'accept-encoding': 'gzip, deflate, br',
    'accept-language': 'zh-CN,zh;q=0.9',
    'cookie': '__jdc=122270672; areaId=19; __jdu=1661337424; PCSYCityID=1655; shshshfpa=7e74a64b-56c7-33b4-15b9-eb7779500e56-1558594766; ipLoc-djd=1-72-2799-0; shshshfpb=gCjtpg%2FzwvN%20OT9HV9WQ3Fw%3D%3D; mt_xid=V2_52007VwMVVV1aUFwaShxsDTIGF1JVUAJGSEhNCRliBkBQQVEGU0pVH14FbgpGVl5aAlsbeRpdBW8fE1ZBW1BLH00SXAxsAhtiX2hSahZAG14NYAsQU1pQUlMWTh9YAmUzF1Q%3D; user-key=33306b43-72c1-4160-8a24-9e3c20cf2e3d; cn=0; unpl=V2_ZzNtbUFQREF1CUYDL0oMUmIGEV8RAkNGdw1BVCtOXVVjUUVZclRCFX0UR1BnGl4UZwcZX0NcQBRFCEdkeBBVAWMDE1VGZxBFLV0CFSNGF1wjU00zQwBBQHcJFF0uSgwDYgcaDhFTQEJ2XBVQL0oMDDdRFAhyZ0AVRQhHZHseWwVlARRYQVJzJXI4dmR7EV8DYwciXHJWc1chVEVQeBFfByoDFVpCVUETcAtDZHopXw%3d%3d; __jda=122270672.1661337424.1558594763.1558596092.1558596619.3; __jdv=122270672|baidu-pinzhuan|t_288551095_baidupinzhuan|cpc|0f3d30c8dba7459bb52f2eb5eba8ac7d_0_266e101febaf4423bd1b3461af0a5cf5|1558596618965; 3AB9D23F7A4B3C9B=BPVVL5LYTHJAYCV3HMI67YFSXNEJPVIN3F7PBACOVIWD5CUDVER57J2EUBFFELM6TWGOEH7LEQYC62KL4F22QSLMJM; shshshfp=bf8ed6ff4a2eebc3fc5bdcb839187cc9; JSESSIONID=20E2234A1851D79D2E2C19321B98831D.s1; shshshsID=5a7e3051be447a2db7638cf291c3c449_17_1558596678720; __jdb=122270672.5.1661337424|3.1558596619',
    'referer': 'https://item.jd.com/100000047414.html',
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.25 Safari/537.36 Core/1.70.3676.400 QQBrowser/10.4.3505.400'
}
data=cur.fetchall()
url_format = r"https://sclub.jd.com/comment/productPageComments.action?productId={0}&score=0&sortType=5&page=0&pageSize=10&isShadowSku=0&fold=1"
count = 1
for i in data:
    goodsID = i[0]
    url_command = url_format.format(goodsID)
    try:
        try:
            comm = requests.get(url=url_command,headers=header).text
        except:
            comm = requests.get(url=url_command, headers=header).text
        pattern_command_rate = r'"goodRateShow":(\d+)'
        try:
            command_rate = re.compile(pattern_command_rate).findall(comm)[0] + "%"
        except:
            command_rate = ""
        # print(command_rate)
        # 评价标签
        pattern_sign = r'"id":".*?","name":"(.*?)"'
        try:
            sign = ' '.join(re.compile(pattern_sign).findall(comm))
        except:
            sign = ""
        # print(sign)
        # 评论数
        pattern_num = r'"commentCount":(\d+)'
        try:
            num = re.compile(pattern_num).findall(comm)[0]
        except:
            num = ""
        # print(num)
        cur.execute("UPDATE `京东手机数据` SET `好评率`='{0}', `评价标签`='{1}', `评论数`='{2}' WHERE  (`商品ID`='{3}')".format(command_rate,sign,num,goodsID))
        conn.commit()
        print("第",count,"条数据更新成功!")
    except:
        pass
    count+=1
